これからAmazon Redshiftを始める技術者が注意すべき１１つのポイント

Amazon Redshiftで良く使いそうな便利系SQL

#Amazon Redshift

#AWS

T君

2014.10.07

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

更新版がこちらにありますので、こちらもぜひご覧ください。これからAmazon Redshiftを始める技術者が注意すべき２２つのポイント

Amazon Redshiftを使った実案件を経験してこれは注意したほうがいいなというポイントをまとめました。自分が経験した範囲で書いているので多少偏っているかもしれませんが、参考になれば幸いです。データウェアハウスって何？という方は以前入門記事を書きましたのでこちらの記事をご覧ください。気軽に始めてみよう！クラウド時代のデータウェアハウス超入門

PostgreSQLと違う点に注意！

Amazon RedshiftはPostgreSQLがベースになっています。私はPostgreSQLを実務で使った経験がないのですが、PostgreSQLに慣れている方は違いを以下のページで確認しておいたほうがいいでしょう。サポートされていない機能、関数、データ型などが確認できます。 Amazon Redshift データベース開発者ガイド - Amazon Redshift および PostgreSQL

また、Amazon Redshiftはソートキー、分散キー、圧縮タイプといったPostgreSQLにはないキーや項目があります。以下の記事がわかりやすいと思うのでテーブル設計する際に見るといいと思います。

Amazon Redshift データベース開発者ガイド - Amazon Redshift でのテーブルの設計のベストプラクティス

簡単に特徴をまとめると以下になります。

分散キー: １テーブル１つまで。テーブル作成後に変更はできない。
ソートキー: １テーブル複数作成可能(最大400)。テーブル作成後に変更はできない。
圧縮タイプ: テーブル作成後に変更はできない。未定義かつデータがない状態であればCOPYコマンドでロードすると自動で設定できる。

１件ずつINSERTするととても遅いので注意！

ETLツールなどを使うとDBからDBにデータを流すことができ、とても便利なのですがRedshiftは1件ずつデータをINSERTするのが苦手です。高速にデータをロードしたい場合はCOPYコマンドを使ってデータをロードしましょう。

Amazon Redshift データベース開発者ガイド - Insert

なお１件ずつデータをINSERTするのは苦手ですが、INSERT ... SELECT構文のように一括してINSERTする場合は問題ありません。

主キー制約、一意制約、外部キー制約は違反してもエラーにならないので注意！

CREATE TABLE文で制約の定義は普通にできるのですが、違反しているデータをINSERTしようとしてもエラーにはならずINSERTできてしまいます。なおNOT NULL制約に違反した場合はちゃんとエラーになります。

Amazon Redshift データベース開発者ガイド - プライマリキーおよび外部キーの制約の定義

COPYコマンドは全件INSERTなので注意！

COPYコマンドでデータをロードする場合、同じ主キーのレコードが存在してもそのままロードしてしまいます。上の項目の通り主キー制約違反があった場合でもエラーになりません。ロードしたいテーブルに同じ主キーのレコードが存在する場合は更新させるには以下のページのような一時テーブルを使った方法があります。

Amazon Redshift データベース開発者ガイド - ステージングテーブルを使用したマージ（アップサート）の実行

CSVでアップロードする場合の注意点

COPYコマンドを使って以下のようなデータを含むCSVファイルをロードする場合は注意が必要です。

データにカンマが含まれる場合
データに改行が含まれる場合
データに空文字が含まれる場合
データにNULL文字が含まれる場合

データにカンマや改行が含まれる場合は、COPYコマンドのCSVオプションをつけ、CSVファイルのカンマとカンマの間をダブルクォテーションで囲む必要があります。次にデータに空文字が含まれる場合はNULLとしてDBに格納するのか、空文字としてDBに格納するのかも考えなくてはいけません。空文字の場合はNULLとするのであれば、COPYコマンドのEMPTYASNULLオプションをつける必要があります。最後にテキストデータにNULL文字が含まれているとロードに失敗するので消しておく必要があります。

サポートしているデータ型に注意！

Redshiftのドキュメントを参照すると分かりますが、サポートしているデータ型が結構少なく全部で11種類です。 CREATE TABLE文でTEXT型などこれ以外の型を指定してもエラーは出ませんが、内部的にVARCHARに置き換えられます。思ってなかった桁数になっていることもあるので慣れるまではpg_table_defテーブルで型を確認したほうがよさそうです。ラージ・オブジェクト型やバイナリ型はありませんので注意して下さい。

Amazon Redshift データベース開発者ガイド - データ型

エンコーディングがほとんど選べないので注意！

RedshiftではUTF8、UTF16、UTF16LE、UTF16BE以外のエンコーディングを選択することはできません。COPYコマンドでロードするファイルのエンコーディングがではない場合は変換する必要があります。デフォルトがUTF8となっており、それ以外のUTF16、UTF16LE、UTF16BEの場合はCOPYコマンドのENCODINGオプションで指定する必要があります。他のDBからの移行する場合は注意しましょう。またファイル内にサポートされていないマルチバイト文字があった場合はデフォルトではエラーになりますが、COPYコマンドのACCEPTINVCHARSオプションを使うと指定した文字列に置換することができますので覚えておくといいでしょう。

参照のみのユーザを追加する際の注意点！

これはPostgreSQLの仕様なのですがデフォルトでpublicというスキーマがあります。CREATE USERでユーザを追加した場合にこのスキーマに対するCREATE権限とUSAGE権限はGRANTしなくても持っているので、参照のみのユーザを作成したりする場合は自分でスキーマを作成した方がいいと思います。

Amazon Redshift データベース開発者ガイド - CREATE USER

タイムゾーンに関する注意点！

以下のようにgetdate関数を使うとシステム時間を表示することができますが、タイムゾーンがUTC固定になっているので日本時間で表示されません。

select getdate();

日本時間で表示したい場合は以下のようにタイムゾーンを変換するconvert_timezone関数を使います。

select convert_timezone('JST', getdate());

日付で表示したい場合は以下のようにtrunc関数を使えばいいと思います。もっとシンプルな方法がありましたら教えて下さい。

select trunc(convert_timezone('JST', getdate()));

ノードタイプに関する注意点！

RedshiftではAmazon RDSのようにインスタンスタイプとストレージサイズを設定することができません。Redshiftの場合はノードタイプを選択することで1ノードあたりのストレージサイズが決まるので、あとはノード数を変えることでストレージサイズを調整します。 dw1.xlarge、dw1.8xlargeなどのDW1系とdw2.large、dw2.8xlargeなどのDW2系との大きな違いはストレージがHDDかSSDかの違いになります。大きいストレージが必要な場合はHDDのdw1系を選択します。ノードタイプが8xlargeの場合はシングルノードの構成にはできません。最低でも２つノードを作成する必要があります。ノード数をいくつか決定する際にまずはサイズを目安にするかと思いますが、処理内容によってはCPUがボトルネックになる場合もあるので性能実施後にノード数を決定することをお勧めします。

PostgreSQLクライアントがタイムアウトした場合の注意点！

SQL Workbenchなどを使ってRedshiftに接続する際に処理が重すぎてタイムアウトしてしまうことがあると思います。ですが、この場合はリクエストした処理が止まるわけではありません。Management Consoleを見るとRunning中になっているのがわかります。このような場合は以下のようなクエリでリクエストをキャンセルする必要があります。

-- PIDを特定する
select pid, trim(user_name), starttime, substring(query,1,20) 
from stv_recents
where status='Running';

-- 特定したプロセスをキャンセル
cancel [PID];

タイムアウトで処理が終了したと思っていると知らないうちに負荷がかかり続けることがあるので注意しましょう。CloudWatchでCPU使用率やストレージ使用率を監視できるので設定しておくといいと思います。 Amazon Redshift データベース開発者ガイド - クエリをキャンセルする

おまけ

よく使うSQLです。あとは上で紹介しているPIDを特定、プロセスをキャンセルするのもよく使います。

select * from stl_load_errors; --エラーを見たい場合
select * from pg_tables where schemaname = 'スキーマ名' and tablename='テーブル名'; --テーブル一覧を見たい場合
select * from pg_table_def where schemaname = 'スキーマ名' and tablename='テーブル名'; --カラム一覧を見たい場合

最後に私がよく見ていたRedshiftのおすすめの記事を紹介します。

Amazon Redshiftで良く使いそうな便利系SQLをまとめてみた Amazon Redshift クエリパフォーマンスチューニングベストプラクティスを読んでみた Amazon Redshift データ型と列圧縮タイプのまとめ(データ型＆列圧縮タイプ対応表付)